package com.zjr.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import com.zjr.db.DBConnection;
import com.zjr.domain.User;

public class UserService {
	private Connection conn = null;
	private PreparedStatement stmt = null;
	private ResultSet rs = null;
	private String sql = null;
	
	public User UserLogin(User user){
		User res = null;
		try {
			conn = DBConnection.getConnection();
			sql = "select * from user where username=? and password=?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, user.getUsername());
			stmt.setString(2, user.getPassword());
			rs = stmt.executeQuery();
			if(rs.next()){
				res = new User();
				res.setUid(rs.getInt("uid"));
				res.setUsername(rs.getString("username"));
				res.setPassword(rs.getString("password"));
				res.setDid(rs.getInt("did"));
				res.setPhone(rs.getString("phone"));
				res.setAddress(rs.getString("address"));
				res.setPosition(rs.getInt("position"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnection.Close(rs, stmt, conn);
		}
		
		return res;
	}
	
	public boolean addUser(User user){
		boolean flag = false;
		try {
			conn = DBConnection.getConnection();
			sql = "insert into user "
					+ "(username, password, did, phone, address, position)"
					+ "values"
					+ "(?, ?, ?, ?, ? ,?)";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, user.getUsername());
			stmt.setString(2, user.getPassword());
			stmt.setInt(3, user.getDid());
			stmt.setString(4, user.getPhone());
			stmt.setString(5, user.getAddress());
			stmt.setInt(6, user.getPosition());
			if(stmt.executeUpdate() == 1){
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnection.Close(rs, stmt, conn);
		}
		
		return flag;
	}
	
	public ArrayList<User> queryUser(){
		List<User> users = new ArrayList<User>();
		try {
			conn = DBConnection.getConnection();
			sql = "select * from user";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while(rs.next()){
				User user = new User();
				user.setUid(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPassword(rs.getString(3));
				user.setDid(rs.getInt(4));
				user.setPhone(rs.getString(5));
				user.setAddress(rs.getString(6));
				user.setPosition(rs.getInt(7));
				users.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnection.Close(rs, stmt, conn);
		}
		
		return (ArrayList<User>) users;
	}
	
	public void deleteUserById(int id){
		try {
			conn = DBConnection.getConnection();
			sql = "delete from user where uid = ?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, id);
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnection.Close(rs, stmt, conn);
		}
	}
	
	public User findUserById(int id){
		User user = null;
		try {
			conn = DBConnection.getConnection();
			sql = "select * from user where uid = ?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			while(rs.next()){
				user = new User();
				user.setUid(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPassword(rs.getString(3));
				user.setDid(rs.getInt(4));
				user.setPhone(rs.getString(5));
				user.setAddress(rs.getString(6));
				user.setPosition(rs.getInt(7));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnection.Close(rs, stmt, conn);
		}
		
		return user;
	}
	
	public boolean modifyUser(User user){
		boolean flag = false;
		try {
			conn = DBConnection.getConnection();
			sql = "update user set password=?, did=?, phone=?, address=?, position=? where username=?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, user.getPassword());
			stmt.setInt(2, user.getDid());
			stmt.setString(3, user.getPhone());
			stmt.setString(4, user.getAddress());
			stmt.setInt(5, user.getPosition());
			stmt.setString(6, user.getUsername());
			if(stmt.executeUpdate() > 0){
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnection.Close(rs, stmt, conn);
		}
		
		return flag;
	}
}
